Client Report - Late Flights & Missing Data (JSON)

On this page

• Elevator pitch
• QUESTION|TASK 1
• QUESTION|TASK 2
• QUESTION|TASK 3
• QUESTION|TASK 4
• QUESTION|TASK 5
• STRETCH QUESTION|TASK 1

• Report an issue

import pandas as pd
import numpy as np
import json
from lets_plot import *
import requests

LetsPlot.setup_html(isolated_frame=True)

# Load JSON data
url = 'https://github.com/byuidatascience/data4missing/raw/master/data-raw/flights_missing/flights_missing.json'
response = requests.get(url)
raw_json = response.json()


# Normalize to DataFrame
df = pd.json_normalize(raw_json)

Elevator pitch

A SHORT (2-3 SENTENCES) PARAGRAPH THAT DESCRIBES KEY INSIGHTS TAKEN FROM METRICS IN THE PROJECT RESULTS THINK TOP OR MOST IMPORTANT RESULTS. (Note: this is not a summary of the project, but a summary of the results.)

San Francisco and Chicago O’Hare airports experience the most significant delays, especially during summer. September is the best month to fly for fewer delays. Mild weather delays are more impactful than reported, affecting up to 15% of flights at some airports.

QUESTION|TASK 1

Fix all of the varied missing data types in the data to be consistent (all missing values should be displayed as “NaN”). In your report include one record example (one row) from your new data, in the raw JSON format. Your example should display the “NaN” for at least one missing value.__

All inconsistent missing values were standardized to NaN for analysis. Below is an example row from the cleaned dataset in JSON format, showing at least one missing value. This makes sure future processing treats all missing values uniformly.

# Task 1: Replace inconsistent missing values
df.replace(["", None, -999, "n/a"], np.nan, inplace=True)

# Show example row as JSON (with NaN included)
example_row = df.iloc[2].to_dict()
json.dumps(example_row, indent=2, default=str)

'{\n  "airport_code": "IAD",\n  "airport_name": NaN,\n  "month": "January",\n  "year": 2005.0,\n  "num_of_flights_total": 12381,\n  "num_of_delays_carrier": "414",\n  "num_of_delays_late_aircraft": 1058.0,\n  "num_of_delays_nas": 895,\n  "num_of_delays_security": 4,\n  "num_of_delays_weather": 61,\n  "num_of_delays_total": 2430,\n  "minutes_delayed_carrier": NaN,\n  "minutes_delayed_late_aircraft": 70919,\n  "minutes_delayed_nas": 35660.0,\n  "minutes_delayed_security": 208,\n  "minutes_delayed_weather": 4497,\n  "minutes_delayed_total": 134881\n}'

QUESTION|TASK 2

Which airport has the worst delays? Describe the metric you chose, and why you chose it to determine the “worst” airport. Your answer should include a summary table that lists (for each airport) the total number of flights, total number of delayed flights, proportion of delayed flights, and average delay time in hours.

San Francisco International and Chicago O’Hare had the highest average delays. San Francisco had the longest delays overall, despite fewer delays proportionally, this shows the importance of duration over frequency when talking about disruption severity.

summary = df.groupby("airport_name").agg({
    "num_of_flights_total": "sum",
    "num_of_delays_total": "sum",
    "minutes_delayed_total": "sum"
}).reset_index()

summary["proportion_delayed"] = summary["num_of_delays_total"] / summary["num_of_flights_total"]
summary["avg_delay_hours"] = summary["minutes_delayed_total"] / summary["num_of_flights_total"] / 60

# Sort by average delay
summary_sorted = summary.sort_values("avg_delay_hours", ascending=False).reset_index(drop=True)

# Display top 5 worst airports
summary_sorted.head()

	airport_name	num_of_flights_total	num_of_delays_total	minutes_delayed_total	proportion_delayed	avg_delay_hours
0	San Francisco, CA: San Francisco International	1565257	408631	25488636	0.261063	0.271400
1	Chicago, IL: Chicago O'Hare International	3400032	773122	52165135	0.227387	0.255709
2	Atlanta, GA: Hartsfield-Jackson Atlanta Intern...	4235114	870910	52114971	0.205640	0.205091
3	Washington, DC: Washington Dulles International	773480	152630	9322510	0.197329	0.200878
4	Denver, CO: Denver International	2323376	439964	23660463	0.189364	0.169728

QUESTION|TASK 3

What is the best month to fly if you want to avoid delays of any length? Describe the metric you chose and why you chose it to calculate your answer. Include one chart to help support your answer, with the x-axis ordered by month. (To answer this question, you will need to remove any rows that are missing the Month variable.)

September had the lowest average delays, while July and December had the worst. Flying in early fall is best for minimizing disruption.

month_df = df.dropna(subset=["month"]).copy()
month_df["month"] = month_df["month"].str.capitalize()

monthly_summary = month_df.groupby("month").agg({
    "minutes_delayed_total": "sum",
    "num_of_flights_total": "sum"
}).reset_index()

monthly_summary["avg_delay"] = monthly_summary["minutes_delayed_total"] / monthly_summary["num_of_flights_total"] / 60

month_order = ["January", "February", "March", "April", "May", "June",
               "July", "August", "September", "October", "November", "December"]
monthly_summary["month"] = pd.Categorical(monthly_summary["month"], categories=month_order, ordered=True)
monthly_summary.sort_values("month", inplace=True)

ggplot(monthly_summary, aes(x="month", y="avg_delay")) + \
    geom_line(color='blue') + geom_point(size=3) + \
    labs(title="Average Flight Delay per Month",
         x="Month", y="Avg Delay (Hours)") + \
    theme(axis_text_x=element_text(angle=45, hjust=1))

QUESTION|TASK 4

According to the BTS website, the “Weather” category only accounts for severe weather delays. Mild weather delays are not counted in the “Weather” category, but are actually included in both the “NAS” and “Late-Arriving Aircraft” categories. Your job is to create a new column that calculates the total number of flights delayed by weather (both severe and mild). You will need to replace all the missing values in the Late Aircraft variable with the mean. Show your work by printing the first 5 rows of data in a table. Use these three rules for your calculations:

a. 100% of delayed flights in the Weather category are due to weather  
a. 30% of all delayed flights in the Late-Arriving category are due to weather  
a. From April to August, 40% of delayed flights in the NAS category are due to weather. The rest of the months, the proportion rises to 65%    

Missing values in the Late Aircraft field were filled with the column mean. The results show that weather-related issues often extend beyond officially categorized weather delays, showing hidden weather impact.

df["num_of_delays_late_aircraft"] = df["num_of_delays_late_aircraft"].fillna(
    df["num_of_delays_late_aircraft"].mean())

def calc_weather_delay(row):
    delay = 0
    if not pd.isna(row["minutes_delayed_weather"]):
        delay += row["minutes_delayed_weather"]
    if not pd.isna(row["minutes_delayed_late_aircraft"]):
        delay += 0.3 * row["minutes_delayed_late_aircraft"]
    if not pd.isna(row["minutes_delayed_nas"]):
        if str(row["month"]).lower() in ["april", "may", "june", "july", "august"]:
            delay += 0.4 * row["minutes_delayed_nas"]
        else:
            delay += 0.65 * row["minutes_delayed_nas"]
    return delay

df["total_weather_delay"] = df.apply(calc_weather_delay, axis=1)

# Show relevant fields
df[["airport_code", "month", "minutes_delayed_weather", "minutes_delayed_late_aircraft", 
    "minutes_delayed_nas", "total_weather_delay"]].head()

	airport_code	month	minutes_delayed_weather	minutes_delayed_late_aircraft	minutes_delayed_nas	total_weather_delay
0	ATL	January	36931	104415	207467.0	203109.05
1	DEN	January	21779	70301	36817.0	66800.35
2	IAD	January	4497	70919	35660.0	48951.70
3	ORD	January	24859	160811	364382.0	309950.60
4	SAN	January	4326	38445	21127.0	29592.05

QUESTION|TASK 5

Using the new weather variable calculated above, create a barplot showing the proportion of all flights that are delayed by weather at each airport. Describe what you learn from this graph.

A bar chart shows the proportion of total flights delayed by weather for each airport. Airports like Atlanta and Denver had high weather-related delay rates. This insight can guide airlines and passengers to anticipate seasonal and regional risks.

weather_by_airport = df.groupby("airport_code").agg({
    "total_weather_delay": "sum",
    "num_of_flights_total": "sum"
}).reset_index()

weather_by_airport["prop_weather_delay"] = weather_by_airport["total_weather_delay"] / weather_by_airport["num_of_flights_total"]

ggplot(weather_by_airport, aes(x="airport_code", y="prop_weather_delay")) + \
    geom_bar(stat="identity", fill="#87CEEB") + \
    labs(title="Proportion of Flights Delayed by Weather",
         x="Airport Code", y="Proportion Delayed") + \
    theme(axis_text_x=element_text(angle=45, hjust=1))

STRETCH QUESTION|TASK 1

Which delay is the worst delay? Create a similar analysis as above for Weahter Delay with: Carrier Delay and Security Delay. Compare the proportion of delay for each of the three categories in a Chart and a Table. Describe your results.

Carrier delays are the most frequent, suggesting airline-specific issues are the leading cause of disruptions.

Weather is the second most common.

Security delays don’t really happen.

categories = ["minutes_delayed_weather", "minutes_delayed_carrier", "minutes_delayed_security"]
delay_summary = df[categories + ["num_of_flights_total"]].copy()
for col in categories:
    delay_summary[col] = delay_summary[col].fillna(0)
    delay_summary[col] = delay_summary[col] / delay_summary["num_of_flights_total"]

avg_delays = delay_summary[categories].mean().reset_index()
avg_delays.columns = ["delay_type", "avg_delay_proportion"]
avg_delays["delay_type"] = avg_delays["delay_type"].str.replace("minutes_delayed_", "").str.capitalize()

ggplot(avg_delays, aes(x="delay_type", y="avg_delay_proportion")) + \
    geom_bar(stat="identity", fill="#FF7F50") + \
    labs(title="Average Proportion of Flights Delayed by Type",
         x="Delay Type", y="Proportion of Flights Delayed")